How to Compare Two Values When One Is Null

SQL Tutorial Home

In standard SQL , compare expression involving null values is tricky. The first point to mention is that the only way to compare a column with null is by using the is null or is not null operator. Other important point to mention is that the result of a boolean comparison between two columns can return 3 values: true, false, or unknow(or null).

What is unknow ? Sometimes, when one of the columns has a null value, then the result of the comparison is unknow(or null). Let’s see

WITH test AS (
   SELECT 'John' as first_name, ‘John’ as last_name UNION ALL
   SELECT ‘John’, ‘Peter’ UNION ALL
   SELECT ‘John’, NULL
)
select * from test
first_name last_name
John John
John Peter
John null 
WITH test AS (
   SELECT 'John' as first_name, ‘John’ as last_name UNION ALL
   SELECT ‘John’, ‘Peter’ UNION ALL
   SELECT ‘John’, NULL
)
select first_name = last_name as comparison_result, count(*) 
from test 
group by 1;
comparison_result count
true 1
false 1
null 1

BigQuery provides an operator called is distinct from which is null aware, so the following query:

WITH test AS (
   SELECT 'John' as first_name, ‘John’ as last_name UNION ALL
   SELECT ‘John’, ‘Peter’ UNION ALL
   SELECT ‘John’, NULL
)
select first_name is distinct from last_name as comparison_result, count(*) 
from test 
group by 1;
comparison_result count
true 2
false 1

The previous result is a proof that the operator is distinct from is null aware.

IN THIS PAGE